package com.coolgor.xutil.SQL;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.coolgor.xutil.Common.DateOPT;

public class DataOperationImp implements DataOperation {
	
	protected String name=null;
 	protected Connection connection=null; 
	protected Statement statement=null; 
	protected ResultSet resultSet=null; 
	protected int loop = 99; //循环获取数据库连接的次数，若为0则表示不循环,若为-1则表示无限循环。
	
	public DataOperationImp(String name){
		this.name = name;
		try {
			this.connection = DataSourceManager.getConnection(name);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	 
//begin 使用SQL对数据库操作	

	//在数据库连接池中获取连接。
	@Override
	public Connection getConnection() throws SQLException, InterruptedException{
		if (null==connection)
			try {
				connection = DataSourceManager.getConnection(name);
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		int i = this.loop;
		while (null==connection && i--!=0) {
			try {
				//线程暂停3秒，再尝试获取链接！直到获取到链接为止！
				Thread.sleep(3000);
//				wait(3000);
				connection = DataSourceManager.getConnection(name);
			} catch (InterruptedException e) {
				e.printStackTrace();	
	    		String path = this.getClass().getResource("/").getPath() ;
		    	java.util.Date now = new java.util.Date();
		    	java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				com.coolgor.xutil.File.FileIO.AppendToFile(path+"xlog"+DateOPT.getNowTime("yyyyMMdd")+".txt",
						"\n["+dateFormat.format(now)+">>>>>>>>>>>>>>>>>>>>>>>>>>>>\n"+e.toString());
				throw e;
			}
		}
		return this.connection;
	}
	
	//用于执行一个非select的SQL语句，用String SQL直接传入。
	@Override
	public boolean update(String sql) throws SQLException,InterruptedException{
		boolean res = true ;
		try {
	    	if (null==connection) this.getConnection();
	    	if (null==statement) statement = connection.createStatement(); 
			statement.executeUpdate( sql ); 
		}catch ( SQLException sqlex ) {
			res = false;
    		System.err.println("!!!SQL running error -->");
    		System.err.println(sql);        	
    		String path = this.getClass().getResource("/").getPath() ;
	    	java.util.Date now = new java.util.Date();
	    	java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			com.coolgor.xutil.File.FileIO.AppendToFile(path+"xlog"+DateOPT.getNowTime("yyyyMMdd")+".txt",
					"\n["+dateFormat.format(now)+">>>>>>>>>>>>>>>>>>>>>>>>>>>>\n"+sqlex.toString());
			sqlex.printStackTrace(); 
			throw sqlex;
		} catch (InterruptedException iEx){
			res = false;
    		System.err.println(sql);
    		iEx.printStackTrace();     		
    		String path = this.getClass().getResource("/").getPath() ;
	    	java.util.Date now = new java.util.Date();
	    	java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			com.coolgor.xutil.File.FileIO.AppendToFile(path+"xlog"+DateOPT.getNowTime("yyyyMMdd")+".txt",
					"\n["+dateFormat.format(now)+">>>>>>>>>>>>>>>>>>>>>>>>>>>>\n"+iEx.toString());
    		throw iEx;
		} finally {
			return res;
		}
	}
	
	//	用于执行一组非select的SQL语句，将SQL组装入Vector中，全部执行成功后才commit。
	@Override
	public boolean executeBatch(java.util.Vector v) throws InterruptedException, SQLException {
		boolean returnValue=false;
		int[] recordCounts=null; 
		try {
	    	if (null==connection) this.getConnection();
	    	if (null==statement) statement = connection.createStatement();
	    	connection.setAutoCommit(false);
	    	for(int i=0;i<v.size();i++)
	    		statement.addBatch((String)v.elementAt(i));
	    	recordCounts=statement.executeBatch();
	    	connection.commit();
	    	returnValue=true;
	    } catch (SQLException sqlEx) {
	    	try {
				connection.rollback();
			} catch (SQLException sqlEx1) {
	    		System.err.println(">>>>RollBack error!");
	    		//sqlEx1.printStackTrace();
	    		throw sqlEx1;
			}
        	String path = this.getClass().getResource("/").getPath() ;
	    	System.err.println("DataOPT error : executeBatch()");
	    	java.util.Date now = new java.util.Date();
	    	java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			com.coolgor.xutil.File.FileIO.AppendToFile(path+"xlog"+DateOPT.getNowTime("yyyyMMdd")+".txt",
					"\n["+dateFormat.format(now)+">>>>>>>>>>>>>>>>>>>>>>>>>>>>\n"+sqlEx.toString());
	    	//将所有回滚的sql语句输出到日志文件XLOG.TXT
	    	for(int i=0;i<v.size();i++){
				com.coolgor.xutil.File.FileIO.AppendToFile(path+"xlog"+DateOPT.getNowTime("yyyyMMdd")+".txt", "RollBack SQL : "+(String)v.elementAt(i));
	    	}
//	    	//打印所有回滚的sql语句
//	    	for(int i=0;i<v.size();i++){
//	    		System.err.println((String)v.elementAt(i));
//	    	}
//	    	sqlEx.printStackTrace();
	    	throw sqlEx;
	    } finally {
	    	//close();
	    	//这里需不需要关闭连接，还需要研究。
	    }
	    return returnValue;
	}
	
	//执行一个select语句，返回一个ResultSet.
	@Override
	public ResultSet select(String sql) throws SQLException,InterruptedException{
		resultSet = null;
		try {
	    	if (null==connection) this.getConnection();
			if (null==statement) statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE); 
			resultSet = statement.executeQuery( sql ); 
		}catch ( SQLException sqlEx ) { 
    		System.err.println("!!!SQL running error -->");
    		System.err.println(sql);	
    		String path = this.getClass().getResource("/").getPath() ;
	    	java.util.Date now = new java.util.Date();
	    	java.text.SimpleDateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			com.coolgor.xutil.File.FileIO.AppendToFile(path+"xlog"+DateOPT.getNowTime("yyyyMMdd")+".txt",
					"\n["+dateFormat.format(now)+">>>>>>>>>>>>>>>>>>>>>>>>>>>>\n"+sqlEx.toString()+"\n"+sql);
			//sqlex.printStackTrace(); 
			throw sqlEx;
		} 
		return resultSet;
	}


	//	执行一个select语句，返回一个CachedRowSetImpl.
	/**
	@Override
	public CachedRowSetImpl executeCRS(String sql) throws SQLException{
		CachedRowSetImpl crs = new CachedRowSetImpl();
		try{
	    	if (null==connection) this.getConnection();
	    	if (null==statement) statement = connection.createStatement();
			ResultSet rs=null;
            rs = statement.executeQuery(sql); 
			crs.populate(rs);
			rs.close();
        }catch(Exception ex){
    		System.err.println("!!!SQL running error -->");
    		System.err.println(sql);
            ex.printStackTrace();
        }
		return crs;		
	}
	*/


	//获取一个表的结构
	@Override
	public boolean desc(String tablename) throws SQLException,InterruptedException{
		try{
	    	if (null==connection) this.getConnection();
	    	if (null==statement) this.statement = connection.createStatement(); 
			ResultSetMetaData rsmd = this.select("select * from "+tablename).getMetaData();
			for (int i=1;i<=rsmd.getColumnCount();i++){
				System.out.print(rsmd.getColumnName(i)+"   ");
				System.out.print(rsmd.getColumnTypeName(i)+"   ");
				System.out.println(rsmd.getColumnDisplaySize(i));
			}
		}catch ( SQLException sqlEx ) { 
    		System.err.println("!!!SQL running error -->");
    		System.err.println("select * from "+tablename);
//			sqlex.printStackTrace(); 
    		throw sqlEx;
		} 
		return true;
	}
//end 使用SQL对数据库操作
	
	 /**
     * 根据表名,判断数据库表是否存在<br>
     * @param tableName
     * @return true or false
	 * @throws SQLException 
	 * @throws InterruptedException 
     */
    //判断某一个表是否存在
    @Override
	public boolean hasTable(String tableName) throws SQLException, InterruptedException {
        boolean result = false;
    	if (null==connection){
			try {
				this.getConnection();
				DatabaseMetaData meta = connection.getMetaData();
		        ResultSet set = meta.getTables (null, null, tableName, null);
//		        while (set.next()) {
		        if (set.next()) {
		        	result = true;
		        }
			} catch (SQLException e) {
//				e.printStackTrace();
				throw e;
			} catch (InterruptedException e) {
//				e.printStackTrace();
				throw e;
			} finally {
		        return result;
			}
        }
		return result;
    } 

	
	//释放资源
	@Override
	public boolean release(){
		boolean res = true;
		try{
			if ( null != this.resultSet ) this.resultSet.close();
			if ( null != this.statement ) this.statement.close();
//			if ( null != this.connection ) DataSourceManager.freeConnection(this.name, this.connection);
			DataSourceManager.freeConnection(this.name, this.connection);
			if ( null != this.connection ) this.connection.close();
		}catch(SQLException sqlEx){ 
//			sqlEx.printStackTrace();
			res = false;
			throw sqlEx;
		} finally {
	        return res;
		}
	}
	
}